require '../db_help/db_help'
class HidoudouService
   def timestamp
      Time.now.strftime("%Y-%m-%d %H:%M:%S")
   end
   
   def get_main_property(props_str,dao)
       return nil if props_str.to_s.strip==''
       result={}
       prop_ary = props_str.split(";")
       prop_ary.each do |it|
            prop = it.split(":")
            if prop[0].to_s=='品牌'
                rs_bid=dao.query("select id from brands where name='#{prop[1]}'")
                if !rs_bid.nil? && rs_bid.num_rows>0
                   brand_id = rs_bid.fetch_row[0] 
                else
                   dao.query("insert into brands(name,status,flag,created_at,updated_at) values ('#{prop[1]}',0,0,'#{timestamp}','#{timestamp}')")
                   brand_id = dao.query("select @@IDENTITY").fetch_row[0]
                end
                result['brand_name']=prop[1]
                result['brand_id']=brand_id
                rs_bid.free
             elsif prop[0].to_s=='款式'
                result['cloth_type']=prop[1]
             elsif prop[0].to_s=='季节'
                result['season']=prop[1]
             elsif prop[0].to_s=='图案'
                 result['pic_design']=prop[1]       
            end
       end
       result
   end
    
   def data_migration
       begin
       db_config1 = {"host" => '118.126.2.138', 'encoding' => 'utf8', 'username' => 'root', 'password' => 'admin', 'database' => 'tao_hidoudou'}
       db_config2 = {"host" => 'localhost', 'encoding' => 'utf8', 'username' => 'root', 'password' => 'admin', 'database' => 'hidoudou_staging'}
       tao_dao = DBHelp::DBHelp.out_initialize(db_config1)
       staging_dao = DBHelp::DBHelp.out_initialize(db_config2)
       
       rs = tao_dao.query("select distinct ti.id,ti.num_iid,ti.price,ti.nick,ti.list_time,ti.delist_time,ti.format_title as name,ti.title,ti.detail_url,tk.click_url,tk.commission_rate,tk.shop_click_url,si.hitao_intro as description,si.tags,"+
       " si.style_name,si.fashion_name,ti.props,ti.props_str,ti.cat1_id,ti.cat1_name,tc.is_parent as cat1_is_parent,ti.cat2_id,ti.cat2_name,ti.pic_url as main_pic "+
       " from tao_item_matchs tim right join tao_items ti on tim.tao_item_id=ti.id "+
       " left join spider_items si on ti.num_iid=si.num_iid "+
       " left join tao_categories tc on ti.cat1_id=tc.t_cid "+
       " left join taoke_items tk on ti.taobaoke_id=tk.id"+
       " where tim.id is not null")
       puts "Total --- " + rs.num_rows.to_s
       rs.each_hash do |row|
           next if row['name'].to_s.strip==''
           item = { 'table'=>'items',
                    'name'=>row['name'],
                    'description'=>row['description'],
                    'tags'=>row['tags'],
                    'main_pic'=>row['main_pic'],
                    'created_at'=>timestamp,
                    'updated_at'=>timestamp}
           #cat1
           if !row['cat1_name'].nil? && row['cat1_name'].to_s.strip!=''
               rs_cid=staging_dao.query("select id from item_categories where name='#{row['cat1_name']}'")
               if !rs_cid.nil? && rs_cid.num_rows>0
                  cat1_id = rs_cid.fetch_row[0] 
               else
                  cat1 = {'table'=>'item_categories','name'=>row['cat1_name'],'is_parent'=>row['cat1_is_parent'],'status'=>0,'flag'=>0,'created_at'=>timestamp,'updated_at'=>timestamp}
                  staging_dao.query(staging_dao.build_sql(cat1))
                  cat1_id = staging_dao.query("select @@IDENTITY").fetch_row[0] 
               end
               item['cat1_id']=cat1_id
               item['cat1_name']=row['cat1_name']
               rs_cid.free
           end
           #cat2
           if !row['cat2_name'].nil? && row['cat2_name'].to_s.strip!=''
               rs_cid=staging_dao.query("select id from item_categories where name='#{row['cat2_name']}'")
                  if !rs_cid.nil? && rs_cid.num_rows>0
                     cat2_id = rs_cid.fetch_row[0] 
                  else
                     cat2 = {'table'=>'item_categories','name'=>row['cat2_name'],'status'=>0,'flag'=>0,'created_at'=>timestamp,'updated_at'=>timestamp}
                     staging_dao.query(staging_dao.build_sql(cat2))
                     cat2_id = staging_dao.query("select @@IDENTITY").fetch_row[0] 
                  end
                  item['cat2_id']=cat1_id
                  item['cat2_name']=row['cat2_name']
                  rs_cid.free
           end
           #style id         
           rs_sid=staging_dao.query("select id from item_styles where name='#{row['style_name']}'")
           if !rs_sid.nil? && rs_sid.num_rows>0
               style_id = rs_sid.fetch_row[0] 
           else
               style = {'table'=>'item_styles','name'=>row['style_name'],'status'=>0,'flag'=>0,'created_at'=>timestamp,'updated_at'=>timestamp}
               staging_dao.query(staging_dao.build_sql(style))
               style_id = staging_dao.query("select @@IDENTITY").fetch_row[0] 
           end
           item['style_id']=style_id
           item['style_name']=row['style_name']
           rs_sid.free
           #fashion id
           rs_fid=staging_dao.query("select id from item_styles where name='#{row['fashion_name']}'")
              if !rs_fid.nil? && rs_fid.num_rows>0
                  fashion_id = rs_fid.fetch_row[0] 
              else
                  fashion = {'table'=>'item_styles','name'=>row['fashion_name'],'status'=>0,'flag'=>1,'created_at'=>timestamp,'updated_at'=>timestamp}
                  staging_dao.query(staging_dao.build_sql(fashion))
                  fashion_id = staging_dao.query("select @@IDENTITY").fetch_row[0] 
              end
              item['fashion_id']=fashion_id
              rs_fid.free
           
           main_prop = get_main_property(row['props_str'],staging_dao)
           item.merge!(main_prop) unless main_prop.nil?
           
           item['want_buy_count'] = rand(200)
           item['bought_count'] = rand(200)
           item['status']=0
           item['flag']=0
           item['name']=row['title'] if row['name'].nil? || row['name'].to_s.strip==''
           
           begin
              staging_dao.query(staging_dao.build_sql(item))
              t_id = staging_dao.query("select @@IDENTITY").fetch_row[0]
              #item alliance
                 item_alliances = tao_dao.query("select repeat_item_id from tao_item_matchs where tao_item_id=#{row['id']}")
                 if !item_alliances.nil? && item_alliances.num_rows>0
                     alliance = {'taoke_link'=>row['click_url'],'item_link'=>row['detail_url'],'price'=>row['price'],'name'=>row['name'],'status'=>0,'shop_name'=>row['nick'],'commission_rate'=>row['commission_rate'],'list_time'=>row['list_time'],'delist_time'=>row['delist_time'],'item_id'=>t_id,'relation_id'=>row['num_iid'],'source_title'=>row['title'],'table'=>'item_alliances','flag'=>99}
                     staging_dao.query(staging_dao.build_sql(alliance))
                     item_alliances.each do |alli|
                         duplicate_items =  tao_dao.query("select ti.id,ti.num_iid,ti.price,ti.nick,ti.list_time,ti.delist_time,ti.format_title as name,ti.title,ti.detail_url,tk.click_url,tk.commission_rate,tk.shop_click_url,si.hitao_intro as description,si.tags,"+                            " si.style_name,ti.props,ti.props_str,ti.cat1_id,ti.cat1_name,ti.cat2_id,ti.cat2_name,ti.pic_url as main_pic "+
                            " from tao_items ti left join spider_items si on ti.num_iid=si.num_iid "+
                            " left join taoke_items tk on ti.taobaoke_id=tk.id" +
                            " where ti.id=#{alli[0]}")
                         duplicate_items.each_hash do |duplicate_item|
                         alliance = {'taoke_link'=>duplicate_item['click_url'],'item_link'=>duplicate_item['detail_url'],'price'=>duplicate_item['price'],'name'=>duplicate_item['name'],'status'=>0,'shop_name'=>duplicate_item['nick'],'commission_rate'=>duplicate_item['commission_rate'],'list_time'=>duplicate_item['list_time'],'delist_time'=>duplicate_item['delist_time'],'item_id'=>t_id,'relation_id'=>duplicate_item['num_iid'],'source_title'=>duplicate_item['title'],'table'=>'item_alliances','flag'=>99}
                         staging_dao.query(staging_dao.build_sql(alliance))
                        end
                     end 
                 end
           rescue Exception => e
              raise e
           end
           tao_dao.query("update tao_items set status=2 where id=#{row['id']}")
       end if !rs.nil? && rs.num_rows>0
       rs.free
       tao_dao.close
       staging_dao.close
   rescue Exception => e
       raise e
   end
   end
end
begin
   HidoudouService.new.data_migration 
rescue Exception => e
   raise e
end

